create database ATM
on
(
  name='ATM',
  filename='D:\bank\ATM.mdf',
  size=5mb,
  maxsize=100mb,
  filegrowth=10%
)
log on
(
  name='ATM_log',
  filename='D:\bank\ATM_log.mdf',
  size=5mb,
  maxsize=100mb,
  filegrowth=10%
)
go

use ATM
go
create table userInfo
(
   customerID int primary key identity(1,1),
   customerName nvarchar(20) not null,
   PID varchar(18) check(len(PID)>=15 or PID >=18) unique not null,
   telephone varchar(20) check(len(telephone)=13 and telephone like '____-________')  not null,
   address nvarchar(50)
)
create table cardInfo
(
  cardID  nvarchar(30) primary key check(len(cardID)=18 and cardID like '1010 3576 ____ ___'),
  curType nvarchar(10) default('RMB'),
  openDate datetime default(getdate()),
  balance  money not null check(balance>=1),
  pass    varchar(6) not null check(len(pass)=6) default('888888'),
  IsReportLoss char(2) not null default('否'),
  customerID  int foreign key references userInfo(customerID)
)
drop table cardInfo
alter table cardInfo add savingType varchar(4) check (savingType in ('活期','定活两便','定期'))
create table transInfo
(
 transId int identity(1,1) primary key,
 transDate date not null default(getdate()),
 cardID nvarchar(30) not null foreign key references cardInfo(cardID),
 transType varchar(4) check(transType='存入' or transType='支取'),
 transMoney money not null check(transMoney>0),
 remark  ntext
)
drop table transInfo
--孙悟空开户，身份证：123456789012345，电话：0716-78989783，地址：北京海淀 
--   开户金额：1000 活期   -卡号：1010 3576 1234 567

--银行开户表
insert into userInfo(customerName,PID,telephone,address) 
select '孙悟空',123456789012345,'0716-78989783','北京海淀'union
select '沙和尚',421345678912345678,'0478-44223333' ,null union
select '唐僧', 321245678912345678,'0478-44443333',null
select * from userInfo
--银行卡信息表
select * from cardInfo 

insert into cardInfo(cardID,savingType,balance,customerID)
select '1010 3576 1234 567','活期',1000,1 union
select '1010 3576 1212 117','定期',1,2 union
select '1010 3576 1212 113','定期',1,3

select * from transInfo

--1.将用户“孙悟空”开卡时的初始密码更改为“611234”
update cardInfo set pass ='611234' where customerID='1'
--2.	用两条SQL语句实现孙悟空要取钱(取200)的操作，先向交易信息表插入一条取钱的交易记录，然后在孙悟空账上的余额减200
--注意：先要将用户孙悟空的用户编号找到，再根据用户编号找到卡号，再根据银行卡号来插入交易记录和修改账上余额
--更新银行卡信息表
select * from cardInfo 
insert into transInfo(cardID,transType ,transMoney)
select '1010 3576 1234 567','支取',200
update cardInfo set balance=balance-200 where cardID='1010 3576 1234 567'

--3.	用同上题一样的方法实现沙和尚存钱的操作(存300)
select * from  cardInfo
insert into transInfo (cardID,transType,transMoney)
select '1010 3576 1212 117','存入',300
update cardInfo set balance=balance+300 where cardID='1010 3576 1212 113'


--4.	唐僧的卡丢了，需要挂失，将唐僧的银行卡的是否挂失字段的值改为“是”
update cardInfo set IsReportLoss='是' where customerID='3'
--5.	查询出最近10天开户的银行卡的信息
select * from cardInfo where  openDate>='2021-03-09' or openDate<= '2021-03-19'
--6.	查询交易金额最大的银行卡信息，子查询实现
select max(transMoney) from transInfo
select*from transInfo where transMoney in(select max(transMoney) from transInfo)
--7.	再交易信息表中，将总的交易金额，支取的交易金额，存入的交易金额查询出来并输出显示
select sum(transMoney) from transInfo
select sum(transMoney) from transInfo where transType='存入'
select sum(transMoney) from transInfo where transType='支取'



